package com.oristand.printer.address.view;

import com.oristand.printer.address.MainApp;
import com.oristand.printer.address.model.LabelDetail;
import com.oristand.printer.address.model.PrintRecord;
import com.oristand.printer.address.model.PrinterConfiguration;
import com.oristand.printer.address.util.*;
import com.zebra.sdk.comm.*;
import com.zebra.sdk.printer.discovery.DiscoveredPrinterDriver;
import com.zebra.sdk.printer.discovery.UsbDiscoverer;
import javafx.application.Platform;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.fxml.FXML;
import javafx.scene.control.*;
import javafx.scene.image.ImageView;
import javafx.scene.input.*;
import javafx.scene.layout.AnchorPane;
import javafx.scene.text.Text;
import javafx.stage.FileChooser;
import javafx.util.Callback;
import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.lang3.StringUtils;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

/**
 * @author chenkailiang
 * @date 2020/9/2
 */
public class PrintRootLayoutController {

    private MainApp mainApp;
    @FXML
    private Label fileNameLabel;
    @FXML
    private Label labelCountLabel;
    @FXML
    private Label labelCountTopLabel;
    @FXML
    private Label fileNameTextLabel;
    @FXML
    private TableView<LabelDetail> labelDetailTable;
    @FXML
    private TableColumn<LabelDetail, String> indexColumn;
    @FXML
    private TableColumn<LabelDetail, String> orderIdColumn;
    @FXML
    private TableColumn<LabelDetail, String> itemCodeColumn;
    @FXML
    private TableColumn<LabelDetail, String> trackingNumberColumn;
    @FXML
    private TableColumn<LabelDetail, String> printColumn;
    @FXML
    private DatePicker startDate = new DatePicker();
    @FXML
    private DatePicker endDate = new DatePicker();
    @FXML
    private TextField fileNameText;
    @FXML
    private TableView<PrintRecord> printedRecordTable;
    @FXML
    private TableColumn<PrintRecord, String> printDateColumn;
    @FXML
    private TableColumn<PrintRecord, String> fileNameColumn;
    @FXML
    private TableColumn<PrintRecord, String> operationColumn;
    @FXML
    private Button printButton;
    @FXML
    private Button openFileButton;
    @FXML
    private ChoiceBox<String> connectionType;
    @FXML
    private ChoiceBox<DiscoveredPrinterDriver> printerListChoiceBox;
    @FXML
    private TextField ipAddressTextField;
    @FXML
    private TextField portTextField;
    @FXML
    private Label ipAddressLabel;
    @FXML
    private Label portLabel;
    @FXML
    private Button editPrinterButton;
    @FXML
    private Button savePrinterButton;
    @FXML
    private Button cancelSavePrinterButton;
    @FXML
    private Label printerLabel;
    @FXML
    private TextField trackingNumberTextField;
    @FXML
    private TextField orderIdTextField;
    @FXML
    private TextField shippingLabelNoFrom;
    @FXML
    private TextField shippingLabelNoTo;
    @FXML
    private Button searchLabelButton;
    @FXML
    private Button testButton;
    @FXML
    private Label trackingNumberLabel;
    @FXML
    private Label orderIdLabel;
    @FXML
    private Label shippingLabelNoLabel;
    @FXML
    private Label toLabel;
    @FXML
    private Label fromLabel;
    @FXML
    private Button resetSearchLabelButton;
    private boolean printAll = true;
    private Integer labelFromNo = 0;
    private Integer labelToNo = 0 ;
    private final String pattern_number = "\\d*";
    private final String pattern_not_number = "[^\\d]";
    private static final Integer MAX_NUMBER = 100;


    /**
     * 初始化
     */
    @FXML
    private void initialize() {
        if (mainApp != null && mainApp.getLatestUploadFile() != null && DbUtil.checkFilePrinted(mainApp.getLatestUploadFile())) {
            setTablePosition(true, 115.0);
        } else {
            setTablePosition(false, 70.0);
        }
        initializePrintLabel();
        initializePrintRecord();
        initializePrintSetting();
    }

    /**
     * 初始化 Print Label
     */
    private void initializePrintLabel(){
        labelDetailTable.setPlaceholder(new Text("No Data"));
        //给shippingLabelFrom和to的输入框加限制，只能输入整数
        shippingLabelNoFrom.textProperty().addListener((ObservableValue<? extends String> observable, String oldValue,
                                String newValue) -> {
                if (!newValue.matches(pattern_number)) {
                    shippingLabelNoFrom.setText(newValue.replaceAll(pattern_not_number, ""));
                }
            });
        shippingLabelNoTo.textProperty().addListener((ObservableValue<? extends String> observable, String oldValue,String newValue) -> {
                if (!newValue.matches(pattern_number)) {
                    shippingLabelNoTo.setText(newValue.replaceAll(pattern_not_number, ""));
                }
            });
        Callback<TableColumn<LabelDetail, String>, TableCell<LabelDetail, String>> printCellFactory
                = new Callback<TableColumn<LabelDetail, String>, TableCell<LabelDetail, String>>() {
            @Override
            public TableCell<LabelDetail, String> call(final TableColumn<LabelDetail, String> param) {
                return new TableCell<LabelDetail, String>() {
                    @Override
                    public void updateItem(String item, boolean empty) {
                        super.updateItem(item, empty);
                        if (empty) {
                            setGraphic(null);
                            setText(null);
                            return;
                        }
                        Button btn = new Button("Print");

                        if (FileUtil.filePrinting(mainApp.getLatestUploadFile())) {
                            btn.setStyle(Constants.BUTTON_STYLE);
                            btn.addEventHandler(MouseEvent.MOUSE_ENTERED, (MouseEvent e) -> btn.setStyle(Constants.BUTTON_HAND));
                            btn.addEventHandler(MouseEvent.MOUSE_EXITED, (MouseEvent e) -> btn.setStyle(Constants.BUTTON_STYLE));
                        } else {
                            btn.setStyle(Constants.BUTTON_GREY);
                            btn.addEventHandler(MouseEvent.MOUSE_ENTERED, (MouseEvent e) -> btn.setStyle(Constants.BUTTON_GREY_HAND));
                            btn.addEventHandler(MouseEvent.MOUSE_EXITED, (MouseEvent e) -> btn.setStyle(Constants.BUTTON_GREY));
                            btn.setDisable(true);
                        }

                        btn.setOnAction(event -> {
                            LabelDetail labelDetail = getTableView().getItems().get(getIndex());
                            doPrintLabel(labelDetail);
                        });
                        setGraphic(btn);
                        setText(null);
                    }
                };

            }
        };
        printColumn.setCellFactory(printCellFactory);
        if (labelDetailTable.getItems() != null && labelDetailTable.getItems().size() > 0) {
            indexColumn.setCellValueFactory(cellData -> cellData.getValue().indexProperty());
            orderIdColumn.setCellValueFactory(cellData -> cellData.getValue().orderIdProperty());
            itemCodeColumn.setCellValueFactory(cellData -> cellData.getValue().itemCodeProperty());
            trackingNumberColumn.setCellValueFactory(cellData -> cellData.getValue().trackingNumberProperty());
            if(labelToNo == 0){
                labelToNo = labelDetailTable.getItems().size();
            }
        }
        labelDetailTable.getSelectionModel().setCellSelectionEnabled(true);

        labelDetailTable.setOnKeyPressed(event -> {
            if (Constants.KEY_CODE_COMBINATION.match(event)) {
                CommonMethodUtil.copySelectionToClipboard(labelDetailTable);
            }
        });


    }

    /**
     * 初始化 Print Record
     */
    private void initializePrintRecord(){
        Callback<TableColumn<PrintRecord, String>, TableCell<PrintRecord, String>> fileNameCellFactory
                = new Callback<TableColumn<PrintRecord, String>, TableCell<PrintRecord, String>>() {
            @Override
            public TableCell<PrintRecord, String> call(final TableColumn<PrintRecord, String> param) {
                return new TableCell<PrintRecord, String>() {
                    @Override
                    public void updateItem(String item, boolean empty) {
                        super.updateItem(item, empty);
                        if (empty) {
                            setGraphic(null);
                            setText(null);
                            return;
                        }
                        PrintRecord record = getTableView().getItems().get(getIndex());
                        Button btn = new Button(record.getFileName());
                        btn.setStyle(Constants.BUTTON_STYLE);
                        btn.addEventHandler(MouseEvent.MOUSE_ENTERED, (MouseEvent e) -> btn.setStyle(Constants.BUTTON_HAND));
                        btn.addEventHandler(MouseEvent.MOUSE_EXITED, (MouseEvent e) -> btn.setStyle(Constants.BUTTON_STYLE));
                        btn.setOnAction(event -> {
                            PrintRecord printRecord = getTableView().getItems().get(getIndex());
                            doShowPrintRecordDetail(printRecord);
                        });
                        setGraphic(btn);
                        setText(null);
                    }
                };
            }
        };
        Callback<TableColumn<PrintRecord, String>, TableCell<PrintRecord, String>> downloadCellFactory
                = new Callback<TableColumn<PrintRecord, String>, TableCell<PrintRecord, String>>() {
            @Override
            public TableCell<PrintRecord, String> call(final TableColumn<PrintRecord, String> param) {
                return new TableCell<PrintRecord, String>() {
                    @Override
                    public void updateItem(String item, boolean empty) {
                        super.updateItem(item, empty);
                        if (empty) {
                            setGraphic(null);
                            setText(null);
                            return;
                        }
                        Button btn = new Button("Download Print Record");
                        btn.setStyle(Constants.BUTTON_STYLE);
                        btn.addEventHandler(MouseEvent.MOUSE_ENTERED, (MouseEvent e) -> btn.setStyle(Constants.BUTTON_HAND));
                        btn.addEventHandler(MouseEvent.MOUSE_EXITED, (MouseEvent e) -> btn.setStyle(Constants.BUTTON_STYLE));
                        btn.setOnAction(event -> {
                            PrintRecord printRecord = getTableView().getItems().get(getIndex());
                            doDownload(printRecord);
                        });
                        setGraphic(btn);
                        setText(null);

                    }
                };
            }
        };
        printedRecordTable.setPlaceholder(new Text("No Data"));
        if (printedRecordTable.getItems() != null && printedRecordTable.getItems().size() > 0) {
            printDateColumn.setCellValueFactory(cellData -> cellData.getValue().printDateProperty());
            fileNameColumn.setCellFactory(fileNameCellFactory);
            operationColumn.setCellFactory(downloadCellFactory);
        }
    }

    /**
     * 初始化 Print Setting
     */
    private void initializePrintSetting(){
        printerListChoiceBox.setDisable(true);
        connectionType.setDisable(true);
        // 初始化打印机配置
        PrinterConfiguration printerConfiguration = PrinterUtil.getPrinter();
        getUsbPrintersAndAddToComboList();
        // network连接方式先不支持
        connectionType.setItems(FXCollections.observableArrayList(Constants.CONNECTION_TYPE_USB));
        // 获取之前的打印机配置
        if (printerConfiguration != null) {
            connectionType.getSelectionModel().select(printerConfiguration.getConnectionType());
            if (Constants.CONNECTION_TYPE_USB.equals(printerConfiguration.getConnectionType())) {
                printerListChoiceBox.setVisible(true);
                setNetworkPrinterVisible(false);
                printerLabel.setText(printerConfiguration.getPrinterName());
                ObservableList<DiscoveredPrinterDriver> list = printerListChoiceBox.getItems();
                for (DiscoveredPrinterDriver driver : list) {
                    if (driver.printerName.equals(printerConfiguration.getPrinterName())) {
                        printerListChoiceBox.getSelectionModel().select(driver);
                    }
                }
            } else {
                printerListChoiceBox.setVisible(false);
                setNetworkPrinterVisible(true);
                ipAddressTextField.setText(printerConfiguration.getIpAddress());
                portTextField.setText(printerConfiguration.getPort());
            }
        } else {
            connectionType.getSelectionModel().selectFirst();
            setNetworkPrinterVisible(false);
        }
        setButtonVisible(true);
        connectionType.getSelectionModel().selectedItemProperty().addListener((observable, oldValue, newValue) -> {
            if (oldValue == null || newValue == null) {
                return;
            }
            setButtonVisible(false);
            if (Constants.CONNECTION_TYPE_USB.equals(newValue)) {
                setNetworkPrinterVisible(false);
                printerListChoiceBox.setVisible(true);
            } else {
                setNetworkPrinterVisible(true);
                printerListChoiceBox.setVisible(false);

            }
        });
        ipAddressTextField.textProperty().addListener((observable, oldValue, newValue) -> setButtonVisible(false));
        portTextField.textProperty().addListener((observable, oldValue, newValue) -> setButtonVisible(false));
        savePrinterButton.setOnAction(event ->
                ProgressStage.of(mainApp.getPrimaryStage(),
                        new SavePrinterConfigurationWork(connectionType, printerListChoiceBox, ipAddressTextField, portTextField,
                                savePrinterButton, cancelSavePrinterButton, editPrinterButton),
                        " Saving...please wait   ").show());
    }

    /**
     * 重置Label搜索框
     */
    @FXML
    public void doResetSearchLabel(){
        orderIdTextField.setText("");
        trackingNumberTextField.setText("");
        shippingLabelNoFrom.setText("");
        shippingLabelNoTo.setText("");
        //重置搜索flag
        printAll = true;
        labelFromNo = 0;
        labelToNo = mainApp.getLabelDetailData().size();
    }

    /**
     * 取消保存打印机配置
     */
    @FXML
    private void cancelSavePrinter(){
        initialize();
    }

    /**
     * 编辑打印机配置
     */
    @FXML
    private void doEditPrinter(){
        setButtonVisible(false);
        printerListChoiceBox.setDisable(false);
        connectionType.setDisable(false);
    }

    /**
     * 设置Network打印机配置显示或隐藏
     *
     * @param flag true/false
     */
    private void setNetworkPrinterVisible(boolean flag){
        ipAddressLabel.setVisible(flag);
        portLabel.setVisible(flag);
        ipAddressTextField.setVisible(flag);
        portTextField.setVisible(flag);
    }

    /**
     * 没有打开文件时，隐藏search按钮，同时table上移，打开后，显示search按钮，table下移
     *
     * @param flag true/false
     * @param value table与AnchorPane上边距
     */
    private void setTablePosition(boolean flag,double value){
        trackingNumberLabel.setVisible(flag);
        orderIdLabel.setVisible(flag);
        trackingNumberTextField.setVisible(flag);
        orderIdTextField.setVisible(flag);
        searchLabelButton.setVisible(flag);
        shippingLabelNoLabel.setVisible(flag);
        shippingLabelNoFrom.setVisible(flag);
        shippingLabelNoTo.setVisible(flag);
        resetSearchLabelButton.setVisible(flag);
        toLabel.setVisible(flag);
        fromLabel.setVisible(flag);
        AnchorPane.setTopAnchor(labelDetailTable,value);
    }

    /**
     * 设置按钮显示或隐藏
     *
     * @param flag true/false
     */
    private void setButtonVisible(boolean flag){
        savePrinterButton.setVisible(!flag);
        cancelSavePrinterButton.setVisible(!flag);
        editPrinterButton.setVisible(flag);
    }

    /**
     * 添加打印机到单选框
     */
    private void getUsbPrintersAndAddToComboList() {
        //清空打印机列表，防止重复添加
        printerListChoiceBox.getItems().clear();
        try {
            DiscoveredPrinterDriver[] discoPrinters = UsbDiscoverer.getZebraDriverPrinters();
            for(DiscoveredPrinterDriver printer :discoPrinters){
                printerListChoiceBox.getItems().add(printer);
            }
        } catch (ConnectionException e) {
            Platform.runLater(()->CommonMethodUtil.tipAlert(Constants.PRINTER_NOT_CONNECTED,e));
        }
    }

    /**
     * 设置打印按钮等样式
     *
     * @param flag true/false
     * @param style 样式
     */
    private void setButtonStyle(boolean flag,String style){
        printButton.setDisable(flag);
        printButton.setStyle(style);
        openFileButton.setDisable(flag);
        openFileButton.setStyle(style);
    }

    /**
     * 设置主界面对象
     *
     * @param mainApp 主界面对象
     */
    public void setMainApp(MainApp mainApp) {
        this.mainApp = mainApp;
        labelDetailTable.setItems(mainApp.getLabelDetailData());
        printedRecordTable.setItems(mainApp.getPrintRecordList());
        labelCountLabel.setText("0");
        labelCountTopLabel.setText("0");
        if(mainApp.getLatestUploadFile() != null){
            fileNameLabel.setText(mainApp.getLatestUploadFile().getName());
            fileNameTextLabel.setText(" File Name： ");
            labelCountLabel.setText(mainApp.getLabelDetailData().size()+"");
            labelCountTopLabel.setText(mainApp.getLabelDetailData().size()+"");
        }
        initialize();
    }

    /**
     * open file
     */
    @FXML
    private void handleOpen() {
        FileChooser fileChooser = new FileChooser();
        FileChooser.ExtensionFilter extFilter = new FileChooser.ExtensionFilter(
                "ZPL files (*.zpl)", "*.zpl");
        fileChooser.getExtensionFilters().add(extFilter);
        File file = fileChooser.showOpenDialog(mainApp.getPrimaryStage());
        if (file != null) {
            ObservableList<LabelDetail> list = FileUtil.getLabelDetailDataFromFile(file);
            mainApp.loadLabelDetailFromFile(file,list);
        }
    }

    /**
     * 打印整个zpl文件
     */
    @FXML
    private void handPrint(){
        setButtonStyle(true,Constants.BUTTON_DISABLE);
        if(checkWhetherToPrint()){
            try {
                TimeUnit.MILLISECONDS.sleep(500L);
                doPrint();
                TimeUnit.MILLISECONDS.sleep(500L);
            } catch (InterruptedException e) {
                CommonMethodUtil.tipAlert(Constants.SOFT_ERROR,e);
                Thread.currentThread().interrupt();
            }
        }
        setButtonStyle(false,Constants.BUTTON_COLOR);
    }

    /**
     * 在打开的zpl文件中搜索label
     */
    @FXML
    private void handleLabelSearch(){
        String trackingNumber = trackingNumberTextField.getText().trim();
        String orderId = orderIdTextField.getText().trim();
        String fromNo = shippingLabelNoFrom.getText().trim();
        String toNo = shippingLabelNoTo.getText().trim();
        //toNo不为空，fromNo一定不能为空；fromNo为空，toNo可以为空
        if(StringUtils.isNotBlank(toNo) && StringUtils.isBlank(fromNo)){
            CommonMethodUtil.tipAlert("please enter shippingLabelNo from",null);
            return;
        }
        //按照筛选条件过滤
        ObservableList<LabelDetail> filterList = mainApp.getLabelDetailData();
        ObservableList<LabelDetail> temp ;
        if(StringUtils.isNotBlank(trackingNumber)){
            temp = FXCollections.observableArrayList();
            temp.addAll(filterList.stream().filter(labelTemp -> trackingNumber.equalsIgnoreCase(labelTemp.getTrackingNumber())).collect(Collectors.toList()));
            filterList = temp;
            printAll = false;
        }
        if(StringUtils.isNotBlank(orderId)){
            temp = FXCollections.observableArrayList();
            temp.addAll(filterList.stream().filter(labelTemp -> orderId.equalsIgnoreCase(labelTemp.getOrderId())).collect(Collectors.toList()));
            filterList = temp;
            printAll = false;
        }
        if(StringUtils.isNotBlank(fromNo)){
            temp = FXCollections.observableArrayList();
            temp.addAll(filterList.stream().filter(labelTemp -> Integer.parseInt(labelTemp.getIndex()) >= Integer.parseInt(fromNo)).collect(Collectors.toList()));
            filterList = temp;
            printAll = false;
        }
        if(StringUtils.isNotBlank(toNo)){
            temp = FXCollections.observableArrayList();
            temp.addAll(filterList.stream().filter(labelTemp -> Integer.parseInt(labelTemp.getIndex()) <= Integer.parseInt(toNo)).collect(Collectors.toList()));
            filterList = temp;
            printAll = false;
        }
        if(StringUtils.isNotBlank(fromNo)){
            labelFromNo = Integer.parseInt(fromNo);
            if(StringUtils.isNotBlank(toNo)){
                labelToNo = Integer.parseInt(toNo);
            }
        }
        if(filterList.size()>0 && (StringUtils.isNotBlank(trackingNumber) || StringUtils.isNotBlank(orderId))){
            labelFromNo = Integer.valueOf(filterList.get(0).getIndex());
            labelToNo = Integer.valueOf(filterList.get(filterList.size()-1).getIndex());
        }
        labelDetailTable.setItems(filterList);
        labelCountLabel.setText(filterList.size()+"");
        labelCountTopLabel.setText(filterList.size()+"");
        initialize();
    }
    /**
     * 判断是否打印
     * @return 判断结果
     */
    private boolean checkWhetherToPrint(){
        //必须先上传文件才能打印，否则提示错误
        File file = mainApp.getLatestUploadFile();
        if(file == null){
            Alert alert = new Alert(Alert.AlertType.WARNING,"",new ButtonType("OK", ButtonBar.ButtonData.OK_DONE));
            alert.setTitle("Error");
            alert.setHeaderText(Constants.NO_FILE);
            alert.showAndWait();
            return false;
        }
        if(labelDetailTable.getItems().size()==0){
            Alert alert = new Alert(Alert.AlertType.WARNING,"",new ButtonType("OK", ButtonBar.ButtonData.OK_DONE));
            alert.setTitle("Tip");
            alert.setHeaderText(Constants.NO_SHIPPING_LABEL_PRINTED);
            alert.showAndWait();
            return false;
        }

        //确认是否需要打印
        Alert alert = new Alert(Alert.AlertType.INFORMATION,"",new ButtonType("YES", ButtonBar.ButtonData.YES),
                new ButtonType("NO", ButtonBar.ButtonData.NO));
        alert.setTitle("Tip");
        int size = labelDetailTable.getItems()==null?0:labelDetailTable.getItems().size();
        if(size>1){
            alert.setHeaderText("Are you sure to print the  "+ size +" labels?");
        }else{
            alert.setHeaderText("Are you sure to print the  "+ size +" label?");
        }
        Optional<ButtonType> buttonType = alert.showAndWait();
        if(buttonType.isPresent()){
            if (buttonType.get().getButtonData().equals(ButtonBar.ButtonData.NO)){
                return false;
            }
        }else{
            return false;
        }
        //文件被打印过，需要添加重复打印提示
        if(DbUtil.checkFilePrinted(file)){
            return checkPrintAgainInput();
        }
        return true;
    }

    /**
     * 重复打印 dialog 确认处理
     *
     * @return true/false
     */
    private boolean checkPrintAgainInput(){
        TextInputDialog dialog = new TextInputDialog();
        dialog.setTitle("Warning");
        ImageView imageView = new ImageView("file:resources/images/warning.png");
        imageView.setFitHeight(50);
        imageView.setFitWidth(50);
        dialog.setGraphic(imageView);
        dialog.setHeaderText("The file has been printed,are you sure to print again? \r\n\r\nIf you want to print again,you need to discard the previous labels");
        dialog.setContentText("" +
                "Please type 'yes' to print again:");
        dialog.getDialogPane().getButtonTypes().clear();
        dialog.getDialogPane().getButtonTypes().addAll(new ButtonType(
                "Print again", ButtonBar.ButtonData.OK_DONE),
                new ButtonType( "Close", ButtonBar.ButtonData.CANCEL_CLOSE));
        dialog.getDialogPane().lookupButton(dialog.getDialogPane().getButtonTypes().get(0)).setDisable(true);

        dialog.getEditor().setMaxWidth(100D);

        dialog.getEditor().textProperty().addListener((observable,oldValue,newValue)->{
            if(Constants.YES.equalsIgnoreCase(newValue.replaceAll(" ",""))){
                dialog.getEditor().setStyle("-fx-text-fill: black;");
                dialog.getDialogPane().lookupButton(dialog.getDialogPane().getButtonTypes().get(0)).setDisable(false);
            }else{
                dialog.getEditor().setStyle("-fx-text-fill: red;");
                dialog.getDialogPane().lookupButton(dialog.getDialogPane().getButtonTypes().get(0)).setDisable(true);
            }
        } );
        Optional<String> result = dialog.showAndWait();
        if (result.isPresent()){
            return Constants.YES.equalsIgnoreCase(result.get().replaceAll(Constants.SPACE,""));
        }else{
            return false;
        }
    }

    /**
     * 搜索打印记录
     */
    @FXML
    private void handSearch(){
        String date1 = "";
        if(startDate.getValue()!=null){
            date1 = startDate.getValue().toString();
        }
        String date2 = "";
        if(endDate.getValue()!=null){
            date2 = endDate.getValue().toString();
        }
        String fileName = fileNameText.getText();
        StringBuilder sb = new StringBuilder("select printDate,fileName,printResult,printLabelCount from printRecord where 1=1  ");
        if(date1.trim().length()>0){
            sb.append(" and substr(printDate,1,10) >= '").append(date1).append("'");
        }
        if(date2.trim().length()>0){
            sb.append(" and substr(printDate,1,10) <= '").append(date2).append("'");
        }
        if(fileName.trim().length()>0){
            sb.append(" and fileName like '%").append(fileName).append("%' ");
        }
        sb.append(" and (julianday() - julianday(printDate)) < 90 order by printDate desc ;");
        try {
            List<PrintRecord> list = DbUtil.printRecordDbUtil.executeQuery(sb.toString(), CommonMethodUtil :: getPrintRecordFromResultSet);
            ObservableList<PrintRecord> observableList = FXCollections.observableArrayList();
            observableList.addAll(list);
            printedRecordTable.setItems(observableList);
            initialize();
        } catch (ClassNotFoundException | SQLException e) {
            Platform.runLater(()->CommonMethodUtil.tipAlert(Constants.DATABASE_ERROR,e));
        }
    }

    /**
     * 重置打印记录搜索框
     */
    @FXML
    public void doReset(){
        startDate.setValue(null);
        endDate.setValue(null);
        fileNameText.setText("");
    }

    /**
     * 打印记录下载
     *
     * @param printRecord 打印记录
     */
    private void doDownload(PrintRecord printRecord){
        //写入csv
        //fileName
        String fileName ="PR-"+ printRecord.getFileName().replace(".zpl","")+".csv";
        FileChooser fileChooser = new FileChooser();
        fileChooser.setTitle("Download");
        fileChooser.setInitialFileName(fileName);
        fileChooser.getExtensionFilters().add(new FileChooser.ExtensionFilter("CSV Files", "*.csv"));
        File file = fileChooser.showSaveDialog(mainApp.getPrimaryStage());
        //如果file等于null,表示取消下载
        if(file==null){
            return;
        }
        try (OutputStream out = new FileOutputStream(file)){
            //查找打印记录
            String sql = "select orderId,itemCode,trackingNumber,printDate from printLabelRecord where fileName = '"+printRecord.getFileName()+"' and printDate = '"+printRecord.getPrintDate()+"'";
            List<LabelDetail> list = DbUtil.printLabelRecordDbUtil.executeQuery(sql, CommonMethodUtil:: getLabelFromResultSet);
            BufferedWriter csvWtriter = new BufferedWriter(new OutputStreamWriter(out, StandardCharsets.UTF_8), 1024);
            csvWtriter.write("\"No.\",\"Sales Order Number\",\"Item Code\",\"Tracking Number\",\"\tPrinted Date\"");
            for(LabelDetail label : list){
                csvWtriter.newLine();
                String content = "\""+label.getIndex()+"\",\""+label.getOrderId()+"\",\""+label.getItemCode()+"\",\""+
                        label.getTrackingNumber()+"\t\",\""+label.getPrintDate()+"\"";
                csvWtriter.write(content);
            }
            csvWtriter.flush();
            csvWtriter.close();
        } catch (Exception e) {
            Platform.runLater(()->CommonMethodUtil.tipAlert(Constants.FILE_DOWNLOAD_FAIL,e));
        }
    }

    /**
     * 打印整个文件或者筛选的label
     */
    @FXML
    private void doPrint(){
        Connection printerConnection = null;
        String time = Constants.FORMAT.format(new Date());
        String md5 = "";
        int printCount = 0;
        try {
            //如果筛选结束没有Label,不进行任何操作
            if(labelDetailTable.getItems().size()==0){
                return;
            }
            File file = mainApp.getLatestUploadFile();
            md5 = DigestUtils.md5Hex(new FileInputStream(file));

            printerConnection = PrinterUtil.getConnection(printerListChoiceBox.getItems());
            if(printerConnection == null){
                CommonMethodUtil.tipAlert(Constants.PRINTER_NOT_CONNECTED,null);
                return;
            }
            printerConnection.open();
            TimeUnit.MILLISECONDS.sleep(1000L);
            mainApp.getPrimaryStage().setTitle(file.getName()+"——Printing.please wait.");
            if(printAll){
                printCount = printLabelByLineNum(file,0,Integer.MAX_VALUE,printerConnection,printCount,DbUtil.printLabelRecordDbUtil,time);
            }else{
                if(labelToNo - labelFromNo >0){
                    //获取labelToNo对应的label的lineNum
                    int fromLineNum = getLineNumberByIndex(labelFromNo);
                    int toLineNum = getLineNumberByIndex(labelToNo);
                    if(fromLineNum == -1 || toLineNum == -1){
                        Platform.runLater(()->CommonMethodUtil.tipAlert(Constants.FILE_NOT_FOUND,null));
                    }
                    printCount = printLabelByLineNum(file,fromLineNum,toLineNum-1,printerConnection,printCount,DbUtil.printLabelRecordDbUtil,time);
                }
                LabelDetail lastLabel = labelDetailTable.getItems().get(labelDetailTable.getItems().size()-1);
                printLastLabel(printerConnection,lastLabel,time,DbUtil.printLabelRecordDbUtil);
                printCount+=labelToNo - labelFromNo +1;
            }
            DbUtil.savePrintRecord(time,md5,"success",printCount,mainApp.getLatestUploadFile().getName());
            TimeUnit.MILLISECONDS.sleep(1000L);
            mainApp.getPrimaryStage().setTitle(MainApp.APP_NAME);
            initialize();
            printAll = true;
        } catch (Exception e) {
            Platform.runLater(()->CommonMethodUtil.tipAlert(Constants.PRINTER_NOT_CONNECTED,e));
            //保存打印失败的记录
            DbUtil.savePrintRecord(time,md5,"fail",printCount,mainApp.getLatestUploadFile().getName());
        }finally {
            PrinterUtil.closeConnection(printerConnection);
        }
    }

    /**
     * 打印指定label
     * @param printerConnection 打印机连接
     * @param lastLabel label
     * @param time 时间
     * @param sqliteUtil sqliteUtil
     * @throws Exception Exception
     */
    private void printLastLabel(Connection printerConnection,LabelDetail lastLabel,String time,SqliteUtil sqliteUtil) throws Exception{
        String string = getStringFromFileBySearch(mainApp.getLatestUploadFile(),lastLabel);
        //根据label信息定位到指定zpl
        if(StringUtils.isNotBlank(string)){
            printerConnection.write(string.getBytes());
            String sql = "insert into printLabelRecord values('" + lastLabel.getOrderId() + "','" + lastLabel.getItemCode() + "','" +
                    lastLabel.getTrackingNumber() + "','" + mainApp.getLatestUploadFile().getName() + "','" + time + "')";
            sqliteUtil.executeUpdate(sql);
        }else{
            CommonMethodUtil.tipAlert(Constants.FILE_NOT_FOUND,null);
        }
    }


    /**
     * 根据label索引获取line_num
     * @param num label的索引
     * @return int
     */
    private int getLineNumberByIndex(Integer num){
        int fromLineNum = -1;
        for(LabelDetail temp : mainApp.getLabelDetailData()){
            if(Integer.valueOf(temp.getIndex()).equals(num)){
                fromLineNum = temp.getLineNumber();
                break;
            }
        }
        return fromLineNum;
    }


    /**
     * 打印指定范围内的label
     * @param file file
     * @param startLineNum 开始页码
     * @param endLineNum 结束页码
     * @param printerConnection 连接
     * @param printCount 打印label数量
     * @param sqliteUtil sqliteUtil
     * @param time time
     * @return 打印label数量
     * @throws SQLException SQLException
     * @throws ClassNotFoundException ClassNotFoundException
     * @throws ConnectionException ConnectionException
     */
    private Integer printLabelByLineNum(File file,int startLineNum,int endLineNum,Connection printerConnection,Integer printCount,SqliteUtil sqliteUtil,String time) throws SQLException, ClassNotFoundException, ConnectionException {
        if(endLineNum<startLineNum){
            return printCount;
        }
        String data = FileUtil.getStringFromZpl(file);
        if(StringUtils.isBlank(data)){
            return printCount;
        }
        String[] arr = data.split(Constants.ZPL_END_ESCAPE);
        //如果endLineNum-startLineNum过大，即单次要求打印的label过多，可能会丢失此打印任务
        //因此将单次打印的label的数量限制到100个label,分批次打印
        for(int j=startLineNum ;j<=endLineNum;j+=MAX_NUMBER){
            System.out.println("分批次打印"+(1+j/MAX_NUMBER));
            for(int i=j;i<j+MAX_NUMBER;i++){
                // 因为lineNum从0计数，所以i=arr.length也不打印
                if(i>=arr.length){
                    break;
                }
                String printZpl = arr[i]+Constants.ZPL_END;
                printerConnection.write(printZpl.getBytes());
                LabelDetail label = FileUtil.getLabelDetailFromTempString(printZpl,i);
                //打印的label是shipping label时记入数据库
                if(label != null){
                    String sql = "insert into printLabelRecord values('" + label.getOrderId() + "','" + label.getItemCode() + "','" +
                            label.getTrackingNumber() + "','" + mainApp.getLatestUploadFile().getName() + "','" + time + "')";
                    sqliteUtil.executeUpdate(sql);
                    printCount++;
                }
            }
        }


        return printCount;
    }

    /**
     * 根据label获取对应的string
     * @param file 文件对象
     * @param labelDetail label对象
     * @return zpl字符串
     */
    private String getStringFromFileBySearch(File file,LabelDetail labelDetail) {
        StringBuilder result = new StringBuilder();
        try {
            String data = FileUtil.getStringFromZpl(file);
            if(StringUtils.isBlank(data)){
                Platform.runLater(()->CommonMethodUtil.tipAlert(Constants.FILE_NOT_FOUND,null));
                return "";
            }
            String[] arr = data.split(Constants.ZPL_END_ESCAPE);
            for(int index = 0;index<arr.length;index++){
                String temp = arr[index];
                if(temp.length()<50){
                    continue;
                }
                String itemCode = FileUtil.getPatternString(FileUtil.PATTERN_ITEMCODE,temp);
                String orderId = FileUtil.getPatternString(FileUtil.PATTERN_ORDERID,temp);
                String trackingNumber = FileUtil.getPatternString(FileUtil.PATTERN_TRACKINGNUMBER_UPDATE,temp);
                if(trackingNumber!=null){
                    trackingNumber = trackingNumber.replaceAll(Constants.ZPL_END_ESCAPE,"");
                }
                if(StringUtils.isNotBlank(itemCode) && StringUtils.isNotBlank(orderId) && StringUtils.isNotBlank(trackingNumber)
                     && labelDetail.getItemCode().equals(itemCode) && labelDetail.getTrackingNumber().equals(trackingNumber)
                     && labelDetail.getOrderId().equals(orderId)) {
                    int count = labelDetail.getAttachmentNumber();
                    result.append(temp).append(Constants.ZPL_END);
                    if(count>0){
                        for(int i=0;i<count;i++){
                            String str1 = "";
                            if(index+i<arr.length){
                                str1 = arr[index+1+i];
                            }
                            LabelDetail labelDetail11 = FileUtil.getLabelDetailFromTempString(str1,index+2+i);
                            if( labelDetail11 == null){
                                result.append(arr[index+1+i]).append(Constants.ZPL_END);
                            }
                        }
                    }
                    break;
                }
            }
        } catch (Exception e) {
            Platform.runLater(()->CommonMethodUtil.tipAlert(Constants.FILE_NOT_FOUND,e));
        }

        return result.toString();
    }

    /**
     * 打印测试label
     */
    @FXML
    public void printTestLabel(){
        Connection connection = null;
        testButton.setVisible(true);
        try {
            SelectionModel<DiscoveredPrinterDriver> selectionModel = printerListChoiceBox.getSelectionModel();
            DiscoveredPrinterDriver driver = selectionModel.getSelectedItem();
            if(driver == null || driver.printerName.isEmpty()){
                CommonMethodUtil.tipAlert(Constants.PRINTER_NOT_CHOOSE,null);
                return;
            }
            connection = new DriverPrinterConnection(driver.printerName);
            connection.open();
            connection.write(Constants.TEST_LABEL.getBytes());
        } catch (ConnectionException e) {
            Platform.runLater(()->CommonMethodUtil.tipAlert(Constants.PRINTER_NOT_CONNECTED,e));
        } finally {
            PrinterUtil.closeConnection(connection);
        }
    }

    /**
     * 打印指定label
     *
     * @param labelDetail 指定的label
     */
    private void doPrintLabel(LabelDetail labelDetail){
        Connection printerConnection = null;

        try {
            //验证
            Alert alert = new Alert(Alert.AlertType.CONFIRMATION,"",new ButtonType("NO", ButtonBar.ButtonData.NO),
                    new ButtonType("YES", ButtonBar.ButtonData.YES));
            alert.setTitle("Tip");
            alert.setHeaderText("Are you sure to print the label?");
            Optional<ButtonType> buttonType = alert.showAndWait();
            if(buttonType.isPresent()){
                if(buttonType.get().getButtonData().equals(ButtonBar.ButtonData.NO)){
                    return;
                }
            }else{
                return;
            }
            String md5 = DigestUtils.md5Hex(new FileInputStream(mainApp.getLatestUploadFile()));
            //查询数据库判断是否打印过
            String printRecordSql = "select count(*) from printLabelRecord where orderId = '"+labelDetail.getOrderId()+"' and itemCode = '"+labelDetail.getItemCode()+
                    "' and trackingNumber = '"+labelDetail.getTrackingNumber()+"';";
            int count  = DbUtil.printLabelRecordDbUtil.executeQuery(printRecordSql);
            if(count>0 && !checkPrintAgainInput()){
                return;
            }
            String time = Constants.FORMAT.format(new Date());
            printerConnection = PrinterUtil.getConnection(printerListChoiceBox.getItems());
            if(printerConnection == null){
                CommonMethodUtil.tipAlert(Constants.PRINTER_NOT_CONNECTED,null);
                return;
            }
            printerConnection.open();
            String string = getStringFromFileBySearch(mainApp.getLatestUploadFile(),labelDetail);
            //根据label信息定位到指定zpl
            if(StringUtils.isNotBlank(string)){
                printerConnection.write(string.getBytes());
                String sql = "insert into printLabelRecord values('" + labelDetail.getOrderId() + "','" + labelDetail.getItemCode() + "','" +
                        labelDetail.getTrackingNumber() + "','" + mainApp.getLatestUploadFile().getName() + "','" + time + "')";
                DbUtil.printLabelRecordDbUtil.executeUpdate(sql);
                DbUtil.savePrintRecord(time,md5,"success",1,mainApp.getLatestUploadFile().getName());
            }else{
                CommonMethodUtil.tipAlert(Constants.FILE_NOT_FOUND,null);
            }
        } catch (IOException | ClassNotFoundException e) {
            Platform.runLater(()->CommonMethodUtil.tipAlert(Constants.FILE_NOT_FOUND,e));
        } catch (SQLException e) {
            Platform.runLater(()->CommonMethodUtil.tipAlert(Constants.DATABASE_ERROR,e));
        } catch (ConnectionException e) {
            Platform.runLater(()->CommonMethodUtil.tipAlert(Constants.PRINTER_NOT_CONNECTED,e));
        }finally {
            PrinterUtil.closeConnection(printerConnection);
        }

    }

    /**
     * 展示选中的打印记录对应的label
     *
     * @param printRecord 打印记录
     */
    private void doShowPrintRecordDetail(PrintRecord printRecord){
        List<LabelDetail> list = DbUtil.getLabelByPrintRecord(printRecord);
        ObservableList<LabelDetail> selectPrintRecordLabelList = FXCollections.observableArrayList();
        selectPrintRecordLabelList.clear();
        if(list != null){
            selectPrintRecordLabelList.addAll(list);
        }
        mainApp.setSelectPrintRecordLabelList(selectPrintRecordLabelList);
        mainApp.setSelectedPrintRecord(printRecord);
        mainApp.showSelectedPrintRecordLabelDialog();
    }








}
